﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Security.Cryptography;
using System.Text;
using System.Threading.Tasks;

namespace SQLTestsIvan
{
    class Program
    {
        static void Main(string[] args)
        {
            //step 1
           // TestConnection();
            //step2
            //AddSensorTypes();
            //step3
            //AddSensorMetricUnits();
            // step4
            //AddingTestSensors();
            //setp 5
           // AddingTestMeasurments();
////////////////////////////////////////////////////////////////////////
////////////////////////////////////////////////////////////////////////
            //step 6 Adding mailing lists

            Console.ReadKey();

        }

        static void TestConnection()
        {
            MySql.Data.MySqlClient.MySqlConnection conn;
            string myConnectionString;

            myConnectionString = "server=127.0.0.1;uid=root;database=buildingtomorow;";

            try
            {
                Console.WriteLine("Start Test Connection");
                conn = new MySql.Data.MySqlClient.MySqlConnection(myConnectionString);
                conn.Open();
                conn.Close();
                Console.WriteLine("Connection sucesefull");
            }
            catch (MySql.Data.MySqlClient.MySqlException ex)
            {
                switch (ex.Number)
                {
                    case 0:
                        Console.WriteLine("Cannot connect to server.  Contact administrator");
                        break;
                    case 1045:
                        Console.WriteLine("Invalid username/password, please try again");
                        break;
                    default:
                        Console.WriteLine(ex.ToString());
                        break;
                }
            }

        }
        static void AddSensorTypes()
        {
            try
            {
                MySql.Data.MySqlClient.MySqlConnection conn;
                string myConnectionString;

                myConnectionString = "server=127.0.0.1;uid=root;database=buildingtomorow;";

                MySql.Data.MySqlClient.MySqlCommand cmd;

                conn = new MySql.Data.MySqlClient.MySqlConnection(myConnectionString);
                cmd = new MySql.Data.MySqlClient.MySqlCommand();

                Console.WriteLine("Openning connection");

                conn.Open();
                cmd.Connection = conn;

                cmd.CommandText = "INSERT INTO sensortypes(sensorType) VALUES( @type)";
                cmd.Prepare();
                cmd.Parameters.AddWithValue("@type", "TestType");


                Console.WriteLine("Adding Types");

                cmd.Parameters["@type"].Value = "Teperature";
                cmd.ExecuteNonQuery();
                cmd.Parameters["@type"].Value = "Humidity";
                cmd.ExecuteNonQuery();
                cmd.Parameters["@type"].Value = "Sun Radiation";
                cmd.ExecuteNonQuery();
                conn.Close();

                Console.WriteLine("Closing Connection");

            }
            catch (MySql.Data.MySqlClient.MySqlException ex)
            {
                Console.WriteLine(ex.ToString());
            }
        }
        static void AddSensorMetricUnits()
        {
            try
            {
                MySql.Data.MySqlClient.MySqlConnection conn;
                string myConnectionString;

                myConnectionString = "server=127.0.0.1;uid=root;database=buildingtomorow;";

                MySql.Data.MySqlClient.MySqlCommand cmd;

                conn = new MySql.Data.MySqlClient.MySqlConnection(myConnectionString);
                cmd = new MySql.Data.MySqlClient.MySqlCommand();

                Console.WriteLine("Openning connection");

                conn.Open();
                cmd.Connection = conn;

                cmd.CommandText = "INSERT INTO metricunits(measurmentName) VALUES( @mname)";
                cmd.Prepare();
                cmd.Parameters.AddWithValue("@mname", "TestM");


                Console.WriteLine("Adding Measurments");

                cmd.Parameters["@mname"].Value = "°C";
                cmd.ExecuteNonQuery();
                cmd.Parameters["@mname"].Value = "%";
                cmd.ExecuteNonQuery();
                cmd.Parameters["@mname"].Value = "Ly";
                cmd.ExecuteNonQuery();
                conn.Close();

                Console.WriteLine("Closing Connection");

            }
            catch (MySql.Data.MySqlClient.MySqlException ex)
            {
                Console.WriteLine(ex.ToString());
            }
        }
        static void AddingTestSensors()
        {
            try
            {
                MySql.Data.MySqlClient.MySqlConnection conn;
                string myConnectionString;

                myConnectionString = "server=127.0.0.1;uid=root;database=buildingtomorow;";

                MySql.Data.MySqlClient.MySqlCommand cmd;

                conn = new MySql.Data.MySqlClient.MySqlConnection(myConnectionString);
                cmd = new MySql.Data.MySqlClient.MySqlCommand();

                Console.WriteLine("Openning connection");

                conn.Open();
                cmd.Connection = conn;

                cmd.CommandText = "INSERT INTO sensors(sensorName, SensorStatus, sensorMetricUnitID, sensorTypeID, sensorX, sensorY, sensorZ) VALUES( @name, @status, @munitId, @typeId, @x, @y, @z)";
                cmd.Prepare();
                cmd.Parameters.AddWithValue("@name", "SensorName");
                cmd.Parameters.AddWithValue("@status", 1);
                cmd.Parameters.AddWithValue("@munitId", 1);
                cmd.Parameters.AddWithValue("@typeId", 1);
                cmd.Parameters.AddWithValue("@x",2);
                cmd.Parameters.AddWithValue("@y", 2);
                cmd.Parameters.AddWithValue("@z",2);

                Console.WriteLine("Adding Temperature Sensors");

                for (int i = 1; i < 51; i++)
                {
                cmd.Parameters["@name"].Value = "Teperature Sensor - "+i;
                cmd.Parameters["@status"].Value = 1;
                cmd.Parameters["@munitId"].Value = 1;
                cmd.Parameters["@typeId"].Value = 1;
                cmd.Parameters["@x"].Value = 2;
                cmd.Parameters["@y"].Value = 2;
                cmd.Parameters["@z"].Value = 2;
                cmd.ExecuteNonQuery();
                }
                Console.WriteLine("Adding Humidity Sensors");

                for (int i = 1; i < 21; i++)
                {
                    cmd.Parameters["@name"].Value = "Humidity Sensor - " + i;
                    cmd.Parameters["@status"].Value = 1;
                    cmd.Parameters["@munitId"].Value = 2;
                    cmd.Parameters["@typeId"].Value = 2;
                    cmd.Parameters["@x"].Value = 2;
                    cmd.Parameters["@y"].Value = 2;
                    cmd.Parameters["@z"].Value = 2;
                    cmd.ExecuteNonQuery();
                }

                Console.WriteLine("Adding Sun Radiation Sensors");

                for (int i = 1; i < 11; i++)
                {
                    cmd.Parameters["@name"].Value = "Sun Radiation Sensor - " + i;
                    cmd.Parameters["@status"].Value = 1;
                    cmd.Parameters["@munitId"].Value = 3;
                    cmd.Parameters["@typeId"].Value = 3;
                    cmd.Parameters["@x"].Value = 2;
                    cmd.Parameters["@y"].Value = 2;
                    cmd.Parameters["@z"].Value = 2;
                    cmd.ExecuteNonQuery();
                }
                conn.Close();

                Console.WriteLine("Closing Connection");

            }
            catch (MySql.Data.MySqlClient.MySqlException ex)
            {
                Console.WriteLine(ex.ToString());
            }
        }
        static void AddingTestMeasurments()
        {
            try
            {
                MySql.Data.MySqlClient.MySqlConnection conn;
                string myConnectionString;

                myConnectionString = "server=127.0.0.1;uid=root;database=buildingtomorow;";

                MySql.Data.MySqlClient.MySqlCommand cmd;

                conn = new MySql.Data.MySqlClient.MySqlConnection(myConnectionString);
                cmd = new MySql.Data.MySqlClient.MySqlCommand();

                Console.WriteLine("Openning connection");

                conn.Open();
                cmd.Connection = conn;

                cmd.CommandText = "INSERT INTO measurments(measurmentSensorID, measurmentTimestamp, measurmentValue) VALUES( @sensorID, @timestamp, @value)";
                cmd.Prepare();
                cmd.Parameters.AddWithValue("@sensorID", 1);
                cmd.Parameters.AddWithValue("@timestamp", "2013-04-01 01:01:01");
                cmd.Parameters.AddWithValue("@value", 10.0);

                Console.WriteLine("Adding Temperature Sensors Values");

                Random tsensorDeviation = new Random();

                for (int sensorid = 1; sensorid < 51; sensorid++)
                {
                    cmd.Parameters["@sensorID"].Value = sensorid;

                    //Every 5 min for 2 weeks for all temp sensors sensors
                    int totalvaluecountperday = 12 * 24;
                    double incrementstepangle = 180.0 / totalvaluecountperday;
                    double incrementstepcalc = Math.PI * incrementstepangle / 180.0;
                    double sensorDiv = 9 + 2 * tsensorDeviation.NextDouble();
                    DateTime timestamp = new DateTime(2013, 4, 1);
                    Random dayvariation = new Random();
                    for (int day = 0; day < 14; day++)
                    {
                        double dailyvariation = 1 * dayvariation.NextDouble();
                        for (int i = 0; i < totalvaluecountperday; i++)
                        {
                            cmd.Parameters["@timestamp"].Value = timestamp.ToString("yyyy-MM-dd HH:mm:ss");
                            cmd.Parameters["@value"].Value = (sensorDiv + (25+dailyvariation) * (Math.Sin(incrementstepcalc * i)));
                            cmd.ExecuteNonQuery();
                            timestamp = timestamp.AddMinutes(5);
                        }
                    }


                }

                Console.WriteLine("Adding Humidity Sensors Values");

                Random hsensorDeviation = new Random();

                for (int sensorid = 51; sensorid < 71; sensorid++)
                {
                    cmd.Parameters["@sensorID"].Value = sensorid;

                    //Every 5 min for 2 weeks for all temp sensors sensors
                    int totalvaluecountperday = 12 * 24;
                    double incrementstepangle = 180.0 / totalvaluecountperday;
                    double incrementstepcalc = Math.PI * incrementstepangle / 180.0;
                    double sensorDiv = 9 + 2 * hsensorDeviation.NextDouble();
                    DateTime timestamp = new DateTime(2013, 4, 1);
                    Random dayvariation = new Random();
                    for (int day = 0; day < 14; day++)
                    {
                        double dailyvariation = 1 * dayvariation.NextDouble();
                        for (int i = 0; i < totalvaluecountperday; i++)
                        {
                            cmd.Parameters["@timestamp"].Value = timestamp.ToString("yyyy-MM-dd HH:mm:ss");
                            cmd.Parameters["@value"].Value = (sensorDiv + (80 + dailyvariation) * (Math.Sin(incrementstepcalc * i)));
                            cmd.ExecuteNonQuery();
                            timestamp = timestamp.AddMinutes(5);
                        }
                    }


                }

                Console.WriteLine("Adding Sun Radiation Sensors Values");

                Random srsensorDeviation = new Random();

                for (int sensorid = 71; sensorid < 81; sensorid++)
                {
                    cmd.Parameters["@sensorID"].Value = sensorid;

                    //Every 5 min for 2 weeks for all temp sensors sensors
                    int totalvaluecountperday = 12 * 24;
                    double incrementstepangle = 180.0 / totalvaluecountperday;
                    double incrementstepcalc = Math.PI * incrementstepangle / 180.0;
                    double sensorDiv = 9 + 2 * srsensorDeviation.NextDouble();
                    DateTime timestamp = new DateTime(2013, 4, 1);
                    Random dayvariation = new Random();
                    for (int day = 0; day < 14; day++)
                    {
                        double dailyvariation = 1 * dayvariation.NextDouble();
                        for (int i = 0; i < totalvaluecountperday; i++)
                        {
                            cmd.Parameters["@timestamp"].Value = timestamp.ToString("yyyy-MM-dd HH:mm:ss");
                            cmd.Parameters["@value"].Value = (sensorDiv + (160 + dailyvariation) * (Math.Sin(incrementstepcalc * i)));
                            cmd.ExecuteNonQuery();
                            timestamp = timestamp.AddMinutes(5);
                        }
                    }


                }
                conn.Close();
                Console.WriteLine("Done Adding test values");
            }
            catch (MySql.Data.MySqlClient.MySqlException ex)
            {
                Console.WriteLine(ex.ToString());
            }
        }

        static void AddingMailingList()
        {

        }
        static void AddingUsers()
        {

        }
        static void PairingMailListUsers()
        {
        }
        static void AddingTestRules()
        {

        }
        static void TestSQLRuleEvaluation()
        {

        }


        /// <summary>
        /// Returns a MD5 hash as a string
        /// </summary>
        /// <param name="TextToHash">String to be hashed.</param>
        /// <returns>Hash as string.</returns>
        public static String GetMD5Hash(String TextToHash)
        {
            //Check wether data was passed
            if ((TextToHash == null) || (TextToHash.Length == 0))
            {
                return String.Empty;
            }

            //Calculate MD5 hash. This requires that the string is splitted into a byte[].
            MD5 md5 = new MD5CryptoServiceProvider();
            byte[] textToHash = Encoding.Default.GetBytes(TextToHash);
            byte[] result = md5.ComputeHash(textToHash);

            //Convert result back to string.
            return System.BitConverter.ToString(result);
        }
    }
}
